﻿' ------------------------------------------------------------------------------
' An ImageJob is a unit of work for inserting a single image the
' database. ImageJobs are queued for processing in the ImageQueue. The
' queue causes an ImageJob to do its work by calling the Process()
' method.
'
' Process() proceeds through five steps, raising events after each step
' and when the job starts and ends. The steps are implemented by
' these methods:
'
'     CreateRow()          create a row, populate several columns
'     UpdateFilestream()   reads image content, stores in filestream column
'     UpdateMetadata()     updates only those elements found in the image
'     UpdateLocation()     updates the spatial Location column in the row
'     UpdateDate()         determines the timezone from the location and
'                          updates the datetimeoffset column
' ------------------------------------------------------------------------------
' This file is part of the SQL Server 2008 Technology Demonstration. This
' application was designed and coded by Eric Weitzman, LandTime Inc, for
' Microsoft Corporation. Contact Ed Katibah at Microsoft about this demo.
' Copyright © 2008 Microsoft Corporation. All rights reserved.
' ------------------------------------------------------------------------------

Imports System.Windows.Media.Imaging
Imports SpatialAlbum.Core.EnumImageType

Public Class ImageJob

    Public Event Started()
    Public Event InsertedRow(ByVal thumb As BitmapImage)
    Public Event Filestream()
    Public Event Metadata()
    Public Event Location(ByVal point As LatLong)
    Public Event TimeZoneFound(ByVal tz As TimeZone)
    Public Event Completed(ByVal fname As String, ByVal loc As LatLong, ByVal thumbnail As BitmapImage)
    Public Event ExceptionOccurred(ByVal ex As Exception)

    Private m_fname As String = Nothing
    Private m_guid As System.Guid = Nothing
    Private m_meta As ExifMetadata = Nothing
    Private m_connection As Connection = Nothing
    Private m_imagetype As EnumImageType = eNone

    ' these are determined when the job is processed
    Private m_thumb As BitmapImage = Nothing
    Private m_location As LatLong = Nothing
    Private m_timezone As TimeZone = Nothing

    Private Shared m_table As String = Nothing

#Region " properties "
    Public ReadOnly Property Name() As String
        Get
            Return IO.Path.GetFileName(m_fname)
        End Get
    End Property

    Public ReadOnly Property FullName() As String
        Get
            Return m_fname
        End Get
    End Property
    Public ReadOnly Property ImageType() As EnumImageType
        Get
            Return m_imagetype
        End Get
    End Property
#End Region

    Public Sub New(ByVal fname As String, ByVal connection As Connection)
        m_imagetype = ImageUtils.ImageType(fname)
        Assert(m_imagetype = eJpg, "file is not a jpg image")
        m_fname = fname
        m_connection = connection
        m_table = Schema.Photo
    End Sub

    Private Sub New() ' inaccessible constructor
    End Sub

    Public Sub Process()

        Dim start As DateTime = DateTime.Now
        ReportTrace("adding " & m_fname & " to table " & m_table & " ...")
        m_guid = System.Guid.NewGuid()
        m_meta = New ExifMetadata(m_fname, m_connection.CoordinateOrder)

        Try
            RaiseEvent Started()
            CreateRow() : RaiseEvent InsertedRow(m_thumb)
            UpdateFilestream() : RaiseEvent Filestream()
            UpdateMetadata() : RaiseEvent Metadata()
            UpdateLocation() : RaiseEvent Location(m_location)
            UpdateDate() : RaiseEvent TimeZoneFound(m_timezone)
            RaiseEvent Completed(m_fname, m_location, m_thumb)

        Catch ex As SqlException
            RaiseEvent ExceptionOccurred(ex)
            ReportException(ex)
        End Try
        ReportTrace(String.Format("... loaded image in {0} second(s), {1}", (DateTime.Now - start).TotalSeconds, m_fname))
    End Sub

    Private Sub CreateRow()

        ' get the image, make thumbnails
        Dim image As New BitmapImage(New Uri(m_fname))
        m_thumb = GetThumbnail(image)

        Dim thumbbytes() As Byte = ConvertImageToBytes(m_thumb, eJpg)
        Dim tinythumb As BitmapImage = GetTinyThumbnail(image)
        Dim tinythumbbytes() As Byte = ConvertImageToBytes(tinythumb, eJpg)

        ' insert row into the database
        ReportTrace("creating new row for image ...")
        Try
            Dim cmd As SqlCommand = m_connection.CreateCommand()
            cmd.CommandText = "INSERT INTO " & m_table & " " & _
                "(Image,                       RowGuid,       TinyThumbnail,  Thumbnail,  DateTimeOffset,  FileName,  FilePath,  Description, " & _
                                              "ImageName,     PixelHeight,   PixelWidth,     DpiX,       DpiY) VALUES " & _
                "(CAST('' as varbinary(max)), @RowGuid,      @TinyThumbnail, @Thumbnail, @DateTimeOffset, @FileName, @FilePath, @Description, " & _
                                             "@ImageName,    @PixelHeight,  @PixelWidth,    @DpiX,      @DpiY)"
            cmd.Parameters.AddWithValue("@RowGuid", m_guid)
            cmd.Parameters.AddWithValue("@DateTimeOffset", DateTimeOffset.Now.ToString)
            Dim photoname As String = IO.Path.GetFileName(m_fname)
            cmd.Parameters.AddWithValue("@ImageName", IO.Path.GetFileNameWithoutExtension(photoname))
            cmd.Parameters.AddWithValue("@FileName", photoname)
            cmd.Parameters.AddWithValue("@FilePath", m_fname)
            cmd.Parameters.AddWithValue("@Description", "")
            cmd.Parameters.AddWithValue("@Thumbnail", thumbbytes)
            cmd.Parameters.AddWithValue("@TinyThumbnail", tinythumbbytes)
            cmd.Parameters.AddWithValue("@PixelHeight", image.PixelHeight)
            cmd.Parameters.AddWithValue("@PixelWidth", image.PixelWidth)
            cmd.Parameters.AddWithValue("@DpiX", image.DpiX)
            cmd.Parameters.AddWithValue("@DpiY", image.DpiY)

            ReportSqlCommand("insert row: " & photoname, cmd)

            cmd.ExecuteNonQuery()

            ReportTrace("... inserted new row, thumbnail byte count = " & thumbbytes.GetLength(0))
        Catch ex As Exception
            ReportException(ex)
        End Try

        ' verify the number of bytes inserted for the thumbnail
        Try
            ReportTrace("checking byte count for stored thumbnail ...")
            Dim cmd As SqlCommand = m_connection.CreateCommand()
            cmd.CommandText = "SELECT Len(Thumbnail) FROM " & m_table & " WHERE RowGuid = @RowGuid"
            cmd.Parameters.AddWithValue("@RowGuid", m_guid)
            ReportSqlCommand("check thumbnail length", cmd)
            Dim o As Object = cmd.ExecuteScalar()
            Dim len2 As Integer = CType(o, Integer)
            ReportTrace("... byte count = " & len2)
            Assert(thumbbytes.Length = len2, "byte count mismatch")
        Catch ex As Exception
            ReportException(ex)
        End Try
    End Sub

    Private Sub UpdateFilestream()
        Try
            ReportTrace("loading image to memory ...")
            Dim imagebytes() As Byte = LoadImageBytesFromFile(m_fname)

            ReportTrace("storing image in filestream ...")
            SqlFilestream.SaveBytes(m_guid, imagebytes, Schema.Photo, Schema.ImageFilestreamColumn, m_connection)
            ReportTrace("... image stored")
        Catch ex As Exception
            ReportException(ex)
        End Try

    End Sub

    Private Sub UpdateMetadata()

        Dim cmd As SqlCommand = m_connection.CreateCommand()

        ' build sql command to update row, set parameters for only those
        ' metadata elements that were actually present in the image's metadata

        Dim sb As New System.Text.StringBuilder
        sb.Append("UPDATE ").Append(m_table).Append(" SET")

        Dim n As Integer = 0
        For Each ms As MetaSpec In ExifMetadata.MetaMapper.MetaSpecs.Values
            Dim metaname As String = ms.MetaName
            If m_meta.Metadata.ContainsKey(metaname) Then
                Dim col As String = ms.ColumnName
                If col <> "" Then
                    sb.Append(" ").Append(col).Append(" = @").Append(col).Append(",")
                    Dim par As String = "@" & col
                    Dim val As Object = m_meta.Metadata.Item(metaname)
                    cmd.Parameters.AddWithValue(par, val)
                    n += 1
                End If
            End If
        Next

        If n = 0 Then
            ReportTrace("no metadata to store")
            Return
        End If

        sb.Remove(sb.Length - 1, 1)
        sb.Append(" WHERE RowGuid = @RowGuid")

        ' update the row
        Try
            ReportTrace(String.Format("storing {0} metadata element(s) ...", n))
            cmd.CommandText = sb.ToString
            cmd.Parameters.AddWithValue("@RowGuid", m_guid)
            ReportSqlCommand("store metadata", cmd)
            cmd.ExecuteNonQuery()
            ReportTrace("... stored")
        Catch ex As Exception
            ReportException(ex)
        End Try

    End Sub

    Private Sub UpdateLocation()
        If Not m_meta.Metadata.ContainsKey("WKTLocation") Then Return

        ReportTrace("storing location ...")
        Try
            Dim locpoint As String = CType(m_meta.Metadata.Item("WKTLocation"), String)
            m_location = New LatLong( _
                                CType(m_meta.Metadata.Item("Latitude"), Double), _
                                CType(m_meta.Metadata.Item("Longitude"), Double))
            Dim cmd As SqlCommand = m_connection.CreateCommand()
            cmd.CommandText = "UPDATE Photo SET Location = @Location WHERE RowGuid = @RowGuid"
            cmd.Parameters.AddWithValue("@RowGuid", m_guid)
            cmd.Parameters.AddWithValue("@Location", locpoint)
            ReportSqlCommand("store location", cmd)
            cmd.ExecuteNonQuery()
            ReportTrace("... location stored " & locpoint)
        Catch ex As Exception
            ReportException(ex)
        End Try
    End Sub

    Private Sub UpdateDate()

        If Not m_meta.Metadata.ContainsKey("Latitude") Then Return
        If Not m_meta.Metadata.ContainsKey("Longitude") Then Return
        If Not m_meta.Metadata.ContainsKey("DateTimeOffset") Then Return
        Dim lat As Double? = CType(m_meta.Metadata.Item("Latitude"), Double?)
        Dim lon As Double? = CType(m_meta.Metadata.Item("Longitude"), Double?)
        If lat Is Nothing Or lon Is Nothing Then Return

        m_timezone = TimeZone.SpatialSearchTimeZone(CType(lon, Double), CType(lat, Double), m_connection)
        If m_timezone Is Nothing Then Return

        Dim utctime As DateTime = CType(m_meta.Metadata.Item("DateTimeOffset"), DateTime)
        Dim localtime As DateTimeOffset = MakeDateTimeOffset(utctime, m_timezone.UtcOffset)

        Try
            ReportTrace("storing updated datetimeoffset with timezone ...")
            Dim cmd As SqlCommand = m_connection.CreateCommand()
            cmd.CommandText = _
                    "UPDATE Photo " & _
                    "SET DateTimeOffset = @DateTimeOffset, TimeZoneCode = @TimeZoneCode, " & _
                    "CivTimeZoneCode = @CivTimeZoneCode, UtcOffset = @UtcOffset " & _
                    "WHERE RowGuid = @RowGuid"
            cmd.Parameters.AddWithValue("@DateTimeOffset", localtime)
            cmd.Parameters.AddWithValue("@UtcOffset", m_timezone.UtcOffset)
            cmd.Parameters.AddWithValue("@CivTimeZoneCode", m_timezone.CivTimeZoneCode)
            cmd.Parameters.AddWithValue("@TimeZoneCode", m_timezone.TimeZoneCode)
            cmd.Parameters.AddWithValue("@RowGuid", m_guid)
            ReportSqlCommand("store datetimeoffset", cmd)
            cmd.ExecuteNonQuery()
            ReportTrace("... updated timezone information, datetimeoffset = " & localtime.ToString)
        Catch ex As Exception
            ReportException(ex)
        End Try

    End Sub

    ' Convert a DateTime (in UTC) and time offset (in decimal hours) to a DateTimeOffset

    Private Shared Function MakeDateTimeOffset(ByVal utctime As DateTime, ByVal offsethours As Double) As DateTimeOffset
        Dim offset As Double = Math.Abs(offsethours)
        Dim sign As Integer = CType(IIf(offsethours < 0, -1, 1), Integer)
        Dim houroffset As Integer = CType(Math.Truncate(offset), Integer)
        Dim minuteoffset As Integer = CType((offset - houroffset) * 60, Integer)
        houroffset *= sign
        minuteoffset *= sign
        Dim span As New TimeSpan(houroffset, minuteoffset, 0)
        Dim localtime As DateTime = utctime + span
        Return New DateTimeOffset(localtime, New TimeSpan(houroffset, minuteoffset, 0))
    End Function

End Class
